USE Lib
GO 
---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_xtab') BEGIN
	DROP PROCEDURE tsql.sp_xtab
	PRINT '<<< DROP PROCEDURE tsql.sp_xtab - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.sp_xtab
Type:			User defined stored procedure 
Owner:			
Description:	Emulates the functionality provided by MsAccess CrossTab Queries
Parameters:		@sql = query, table or statement containing raw data
				@row_heading =  Column(s) to group on (i.e. 'EmployeeID, FirstName, LastName')
				@column_heading = Column to be used at the top (i.e. PayEndDate)
				@pivot_column = Column to perform calculation on (i.e. CurPay)
				@pivot_function = Aggregate function to be used (i.e. AVG, SUM, COUNT, MIN,MAX)
				@row_total = Include or not a row total (i.e. TOTAL,VARIANCE,NONE)
				(optional params)
 				@debug = For development only (0 = live mode, 1 = debug (display internal values instead of resultset)
Dependens on:  	none

Usage:			@sql			@row_heading	@column_heading	 @pivot_column		@pivot_function	@row_total
				---------------------------------------------------------------------------------------------
tsql.sp_xtab	'Customers'	,	'CompanyName'	,'City'			,'ContactName'		,'COUNT'	   ,'YES'


Result:			Company	Total	New York 	Boston 		Orlando
				=================================================
				Compaq	68		25			32			11	
				IBM		10		2			7			1	
				Oracle	15		4			9			2	

--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=sp_xtab&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		08/28/2005 - Adrian E Dudau - Procedure created
History:		08/02/2012 - Adrian E Dudau - Added SQL 2008 datatypes
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE PROCEDURE tsql.[sp_xtab] 
  @sql VARCHAR(MAX) 
, @row_heading VARCHAR(1000) 
, @column_heading VARCHAR(1000)
, @pivot_column VARCHAR(1000)
, @pivot_function VARCHAR(255)
, @row_total NVARCHAR(255) = 'NONE'
, @debug TINYINT = 0 

AS

BEGIN
	SET NOCOUNT ON
	SET ANSI_WARNINGS OFF

	DECLARE @SQL_Sub VARCHAR(MAX)
	DECLARE @SQL_Main VARCHAR(MAX)

	DECLARE @SQL_Buffer VARCHAR(MAX)
	DECLARE @Cnt Int 
	DECLARE @Variance_String VARCHAR(1024)

	-- DO NOT CHANGE DATATYPE !!!!
	DECLARE @Col_Buffer sql_variant
	-- DO NOT CHANGE DATATYPE !!!!
		
	SET @SQL_Sub = ''
	SET @SQL_Main = ''
	SET @SQL_Buffer = ''
	SET @Variance_String = ''

	SET @SQL_Main = @SQL_Main + 'SELECT ' + @row_heading 
	SET @SQL_Sub = @SQL_Sub + 'SELECT ' + @row_heading 

-- Dynamic param ------------------------------------------------------------------------------------------------------------
	IF @row_total = 'TOTAL'
	BEGIN
		SET @SQL_Main = @SQL_Main + 	', ' + @pivot_function + '(Total)' + ' AS Total'
		SET @SQL_Sub = @SQL_Sub + 	', ' + @pivot_function + '(' + @pivot_column + ')' + ' AS Total'
	END	
	ELSE IF @row_total = 'VARIANCE'
	BEGIN
		SET @SQL_Main = @SQL_Main + 	', ' + @pivot_function + '(Variance)' + ' AS Variance'
		SET @SQL_Sub = @SQL_Sub + 	', ' + '(VarCol1 - VarColn)' + ' AS Variance'
	END
-- Dynamic param ------------------------------------------------------------------------------------------------------------


-- Dynamic Columns based on input Table 
	SET @SQL_Buffer = @SQL_Buffer + ' DECLARE Temp_Cursor CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR '
	SET @SQL_Buffer = @SQL_Buffer + ' SELECT DISTINCT ' + @column_heading + ' FROM (' + @sql + ') TMP ORDER BY ' + @column_heading 

	-- DEBUG ONLY --------------------------------------------------------------------------------------
	IF @DEBUG = 1
	BEGIN
		PRINT '@SQL_Buffer = ' + ISNULL(@SQL_Buffer,'')
		PRINT ''
	END --IF
	------------------------------------------------------------------------------------------------------------------

	EXECUTE (@SQL_Buffer) 

	SET @Cnt = 1 
	OPEN Temp_Cursor

	FETCH NEXT FROM Temp_Cursor INTO @Col_Buffer

	WHILE @@FETCH_STATUS = 0
	BEGIN

		IF LEN(ISNULL(CAST(@Col_Buffer AS VARCHAR),'')) = 0 
		BEGIN
			SET @SQL_Main = @SQL_Main + ',  MAX(' + 'NULL' + ') AS [Column' + CAST(@Cnt AS VARCHAR) + ']' 
			SET @SQL_Sub = @SQL_Sub + ',  CASE WHEN ' +  @column_heading + ' = NULL THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END AS [Column' + CAST(@Cnt AS VARCHAR) + ']' 
			SET @Variance_String = 'ISNULL(CASE WHEN ' +  @column_heading + ' = NULL THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END,0)'

			IF @DEBUG = 1
				PRINT 'NULL'	
		END

		ELSE IF SQL_VARIANT_PROPERTY(@Col_buffer,'BaseType') IN('int','bigint', 'money', 'numeric','float', 'real', 'smallint', 'smallmoney', 'tinyint', 'varbinary', 'uniqueidentifier','decimal')
		BEGIN
			SET @SQL_Buffer = CAST(@Col_Buffer AS VARCHAR)
			SET @SQL_Main = @SQL_Main + ',  MAX([' + @SQL_Buffer + ']) AS [' + @SQL_Buffer + ']' 
			SET @SQL_Sub = @SQL_Sub + ',  CASE WHEN ' +  @column_heading + ' = ' + @SQL_Buffer + ' THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END AS [' + @SQL_Buffer + ']' 
			SET @Variance_String = 'ISNULL(CASE WHEN ' +  @column_heading + ' = ' + @SQL_Buffer + ' THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END,0)'

			IF @DEBUG = 1
				PRINT 'NUMBER'	
		END

		ELSE IF SQL_VARIANT_PROPERTY(@Col_buffer,'BaseType') IN ('date','datetime','datetime2','datetimeoffset','time','smalldatetime')
		--ABS(ISDATE(@SQL_Buffer)) = 1 
		BEGIN
			SET @SQL_Buffer = CAST(@Col_Buffer AS VARCHAR)
			SET @SQL_Buffer = RTRIM(LEFT(CAST(CONVERT(DATETIME, @SQL_Buffer, 102) AS VARCHAR),11))
			SET @SQL_Main = @SQL_Main + ',  MAX([' + RTRIM(LEFT(CAST(CONVERT(DATETIME, @SQL_Buffer, 102) AS VARCHAR),11)) + ']) AS [' + RTRIM(LEFT(CAST(CONVERT(DATETIME, @SQL_Buffer, 102) AS VARCHAR),11)) + ']' 
			SET @SQL_Sub = @SQL_Sub + ',  CASE WHEN ' +  @column_heading + ' = CONVERT(DATETIME, ' + '''' + @SQL_Buffer + '''' + ',102) THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END AS [' + @SQL_Buffer + ']' 
			SET @Variance_String = 'ISNULL(CASE WHEN ' +  @column_heading + ' = CONVERT(DATETIME, ' + '''' + @SQL_Buffer + '''' + ',102) THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END,0)'

			IF @DEBUG = 1
				PRINT 'DATE = ' + @sql_BUFFER	
		END

		ELSE IF SQL_VARIANT_PROPERTY(@Col_buffer,'BaseType') IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
		BEGIN
			SET @SQL_Buffer = CAST(@Col_Buffer AS VARCHAR)
			SET @SQL_Main = @SQL_Main + ',  MAX([' + @SQL_Buffer + ']) AS [' + @SQL_Buffer + ']' 
			SET @SQL_Sub = @SQL_Sub + ',  CASE WHEN ' +  @column_heading + ' = ' + '''' + @SQL_Buffer + '''' + ' THEN ' + @pivot_function + '(' + @pivot_column + ') ELSE NULL END AS [' + @SQL_Buffer + ']' 

			IF @DEBUG = 1
				PRINT 'OTHER'	
		END	

		IF @Cnt = 1 AND @row_total = 'VARIANCE' 
			SET @SQL_Sub = REPLACE(@SQL_Sub,'VarCol1','('+ @Variance_String +')')

		SET @Cnt = @Cnt + 1
		FETCH NEXT FROM Temp_Cursor INTO @Col_Buffer

		IF @Cnt > 200
		BEGIN
			CLOSE Temp_Cursor
			DEALLOCATE Temp_Cursor
			RAISERROR ( 'Too many columns generated! Please refine your SQL statement.', 16, 1)
			RETURN  
		END

	END -- WHILE

	CLOSE Temp_Cursor
	DEALLOCATE Temp_Cursor

	IF @row_total = 'VARIANCE' 
		SET @SQL_Sub = REPLACE(@SQL_Sub,'VarColn','('+ @Variance_String +')')


	SET @SQL_Sub = @SQL_Sub + ' FROM (' + @sql + ') GRP GROUP BY ' + @row_heading + ', ' + @column_heading 
	SET @SQL_Sub = LTRIM(RTRIM(@SQL_Sub))


	SET @SQL_Main = @SQL_Main + ' FROM (' + @SQL_Sub + ') FTBL GROUP BY ' + @row_heading  + ' ORDER BY ' + @row_heading
	SET @SQL_Main = LTRIM(RTRIM(@SQL_Main))


	IF @DEBUG = 1 BEGIN
		PRINT '@sql(IN) = ' + ISNULL(@sql,'')
		PRINT '@row_heading(IN) = ' + ISNULL(@row_heading,'')
		PRINT '@column_heading(IN) = ' + ISNULL(@column_heading,'')
		PRINT '@pivot_column(IN) = ' + ISNULL(@pivot_column,'')
		PRINT '@pivot_function(IN) = ' + ISNULL(@pivot_function,'')
		PRINT '(optional IN params) ---------------------------------------------------------------------' 
		PRINT '@row_total(IN) = ' + ISNULL(@row_total,'')
		PRINT '@DEBUG(IN) = ' + CAST(ISNULL(@DEBUG,0) AS VARCHAR) 
		PRINT '(Internal Variables) ----------------------------------------------------------------------' 
		PRINT '@SQL_Buffer = ' + ISNULL(@SQL_Buffer,'')
		PRINT '@Col_Buffer = ' + ISNULL(CAST(@Col_Buffer AS VARCHAR),'')
		PRINT 'BaseType = ' + CAST(SQL_VARIANT_PROPERTY(@Col_buffer,'BaseType') AS VARCHAR)
		PRINT '@SQL_Main = ' + ISNULL(@SQL_Main,'')
		PRINT '@SQL_Sub = ' + ISNULL(@SQL_Sub,'')
		PRINT '@Cnt = ' + ISNULL(CAST(@Cnt AS VARCHAR),'')
		PRINT '@Variance_String = ' + ISNULL(@Variance_String,'')
	END
	ELSE IF LEN(@SQL_Sub) > 7950 BEGIN
		CLOSE Temp_Cursor
		DEALLOCATE Temp_Cursor
		RAISERROR ( 'Generated SQL statement exceeds 8000 characters! Please refine your SQL statement.', 16, 1)
		RETURN  
	END


	IF @DEBUG = 0 
		EXECUTE (@SQL_Main)

	SET ANSI_WARNINGS ON
END

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------

IF EXISTS(SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_xtab') BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_xtab - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_xtab - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GRANT SELECT TO PUBLIC 